"""
角色管理相关的数据库操作（增量权限分配 + 无抖动）
"""
from typing import List, Optional, Tuple

from app.models.system_models import Role, Permission, RoleType
from app.schemas.system_schemas import RoleCreate, RoleUpdate, RoleListRequest, RoleWithPermissionsResponse
from common.database import DatabaseService
from common.exceptions import BizException, ErrorCode


# ---------- 基础查询 ----------
async def get_role_by_id(db: DatabaseService, role_id: int) -> Optional[Role]:
	sql = """
          SELECT id,
                 role_code,
                 role_name,
                 description,
                 type,
                 enabled,
                 deleted,
                 version,
                 gmt_create,
                 gmt_update
          FROM base_role
          WHERE id = %s
            AND deleted = 0 \
		  """
	row = db.get_one(sql, (role_id,))
	return Role(**row) if row else None


async def get_role_by_code(db: DatabaseService, role_code: str) -> Optional[Role]:
	sql = """
          SELECT id,
                 role_code,
                 role_name,
                 description,
                 type,
                 enabled,
                 deleted,
                 version,
                 gmt_create,
                 gmt_update
          FROM base_role
          WHERE role_code = %s
            AND deleted = 0 \
		  """
	row = db.get_one(sql, (role_code,))
	return Role(**row) if row else None


# ---------- 角色写 ----------
async def create_role(db: DatabaseService, role_data: RoleCreate) -> int:
	if await get_role_by_code(db, role_data.role_code):
		raise BizException.from_error_code(ErrorCode.CONFLICT, message=f"角色编码 {role_data.role_code} 已存在")
	sql = """
          INSERT INTO base_role (role_code, role_name, description, type, enabled, deleted, version, gmt_create,
                                 gmt_update)
          VALUES (%s, %s, %s, %s, 1, 0, 1, NOW(), NOW()) \
		  """
	return db.insert(sql, (role_data.role_code, role_data.role_name, role_data.description, role_data.type.value))


async def update_role(db: DatabaseService, role_id: int, role_data: RoleUpdate) -> bool:
	if not await get_role_by_id(db, role_id):
		raise BizException.from_error_code(ErrorCode.NOT_FOUND, message="角色不存在")
	fields, params = [], []
	if role_data.role_name is not None:
		fields.append("role_name = %s")
		params.append(role_data.role_name)
	if role_data.description is not None:
		fields.append("description = %s")
		params.append(role_data.description)
	if role_data.enabled is not None:
		fields.append("enabled = %s")
		params.append(role_data.enabled)
	if not fields:
		return True
	fields.extend(["version = version + 1", "gmt_update = NOW()"])
	params.append(role_id)
	sql = f"UPDATE base_role SET {', '.join(fields)} WHERE id = %s AND deleted = 0"
	return db.update(sql, params) > 0


async def delete_role(db: DatabaseService, role_id: int) -> bool:
	role = await get_role_by_id(db, role_id)
	if not role:
		raise BizException.from_error_code(ErrorCode.NOT_FOUND, message="角色不存在")
	cnt = db.get_one("SELECT COUNT(*) FROM base_user_role WHERE role_id = %s", (role_id,))["COUNT(*)"]
	if cnt:
		raise BizException.from_error_code(ErrorCode.CONFLICT, message=f"角色已被 {cnt} 个用户引用，无法删除")
	sql = "UPDATE base_role SET deleted = 1, version = version + 1, gmt_update = NOW() WHERE id = %s"
	return db.update(sql, (role_id,)) > 0


# ---------- 角色列表（含权限） ----------
async def get_role_list(db: DatabaseService, request: RoleListRequest) -> Tuple[int, List[RoleWithPermissionsResponse]]:
	where, params = ["r.deleted = 0"], []
	if request.role_name:
		where.append("r.role_name LIKE %s")
		params.append(f"%{request.role_name}%")
	if request.role_code:
		where.append("r.role_code LIKE %s")
		params.append(f"%{request.role_code}%")
	if request.type is not None:
		try:
			type_enum = RoleType(request.type)  # 校验合法性
		except ValueError:
			raise BizException.from_error_code(
				ErrorCode.BAD_REQUEST,
				message=f"无效角色类型: {request.type}"
			)
		where.append("type = %s")
		params.append(type_enum.value)
	if request.enabled is not None:
		where.append("r.enabled = %s")
		params.append(request.enabled)
	where_clause = " AND ".join(where)

	total = db.get_one(f"SELECT COUNT(*) FROM base_role r WHERE {where_clause}", params)["COUNT(*)"] or 0

	offset = (request.page - 1) * request.size
	sql = f"""
        SELECT r.id, r.role_code, r.role_name, r.description, r.type, r.enabled, r.version, r.gmt_create, r.gmt_update
        FROM base_role r
        WHERE {where_clause}
        ORDER BY r.gmt_create DESC
        LIMIT %s OFFSET %s
    """
	params.extend([request.size, offset])
	rows = db.get_all(sql, params)

	roles = []
	for row in rows:
		permissions = await get_permissions_by_role_id(db, row["id"])
		roles.append(RoleWithPermissionsResponse(**row, permissions=permissions))
	return total, roles


# ---------- 权限查询 ----------
async def get_permissions_by_role_id(db: DatabaseService, role_id: int) -> List[Permission]:
	sql = """
          SELECT p.id,
                 p.perm_code,
                 p.perm_name,
                 p.type,
                 p.parent_id,
                 p.path,
                 p.level,
                 p.sort,
                 p.deleted,
                 p.gmt_create
          FROM base_permission p
                   JOIN base_role_permission rp ON p.id = rp.perm_id
          WHERE rp.role_id = %s
            AND p.deleted = 0
          ORDER BY p.level, p.sort, p.id \
		  """
	rows = db.get_all(sql, (role_id,))
	return [Permission(**r) for r in rows]


# ---------- 增量权限分配（无抖动） ----------
async def assign_permissions_to_role(db: DatabaseService, role_id: int, permission_ids: List[int]) -> bool:
	if not await get_role_by_id(db, role_id):
		raise BizException.from_error_code(ErrorCode.NOT_FOUND, message="角色不存在")
	old_ids = {p.id for p in await get_permissions_by_role_id(db, role_id)}
	new_ids = set(permission_ids)
	to_del, to_add = old_ids - new_ids, new_ids - old_ids
	if to_del:
		placeholders = ",".join(["%s"] * len(to_del))
		db.update(f"DELETE FROM base_role_permission WHERE role_id = %s AND perm_id IN ({placeholders})",
				  [role_id, *to_del])
	if to_add:
		sql = "INSERT INTO base_role_permission (role_id, perm_id) VALUES (%s, %s)"
		for pid in to_add:
			db.insert(sql, (role_id, pid))
	return True


async def remove_permissions_from_role(db: DatabaseService, role_id: int,
									   permission_ids: Optional[List[int]] = None) -> bool:
	if not await get_role_by_id(db, role_id):
		raise BizException.from_error_code(ErrorCode.NOT_FOUND, message="角色不存在")
	if permission_ids:
		placeholders = ",".join(["%s"] * len(permission_ids))
		db.update(f"DELETE FROM base_role_permission WHERE role_id = %s AND perm_id IN ({placeholders})",
				  [role_id, *permission_ids])
	else:
		db.update("DELETE FROM base_role_permission WHERE role_id = %s", (role_id,))
	return True
